/*
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */
package utopiaintelpackage.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

import main.CommonMethods;

import org.apache.log4j.Logger;

import utopiaintelpackage.sos.SoS;
import database.DBConnectionFactory;

/**
 * A class that saves and retreives SoS objects
 * 
 * 
 */
public class SoStoDB {
   /**
    * If a province doesn't exist yet, it needs be added
    * 
    * @param som
    */
   private static void addProvince(final SoS sos) {
	  PreparedStatement ps = null;
	  try {
		 if (!CommonMethods.kdExists(sos.getKdloc())) {
			CommonMethods.addDefaultKD(sos.getKdloc());
		 }

		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("INSERT INTO province VALUES (?,?,?,?,?,?,?,?,?,false,?)");
		 ps.setString(1, sos.getName());
		 ps.setString(2, "");
		 ps.setString(3, "");
		 ps.setString(4, "");
		 ps.setString(5, "");
		 ps.setString(6, sos.getKdloc());
		 ps.setInt(7, 0);
		 ps.setInt(8, 0);
		 ps.setInt(9, 0);
		 ps.setTimestamp(10, new Timestamp(sos.getUpdated().getTime()));
		 ps.executeUpdate();
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
   }

   public static SoS getSoS(final String name) {
	  SoS sos = null;
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM " + "province,sos WHERE sos.name = province.name AND" + " sos.name = ?");
		 ps.setString(1, name);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			sos = new SoS(0);
			sos.setName(rs.getString("province.name"));
			sos.setKdloc(rs.getString("kdloc"));
			sos.setLand(rs.getInt("land"));
			sos.setSciences(rs.getString("sciences"));
			sos.setRace(rs.getString("race"));
			sos.setPersonality(rs.getString("personality"));
			sos.setUpdated(new Date(rs.getTimestamp("updated").getTime()));
			sos.setExportLine(rs.getString("export"));
			sos.setTotalSciencePoints(rs.getInt("totalpoints"));
			sos.setSavedBy(rs.getString("savedby"));
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return sos;
   }

   public static String saveSoS(final SoS sos) {
	  if (!"".equals(CommonMethods.provinceExists(sos.getName()))) {
		 updateProvince(sos);
	  } else {
		 addProvince(sos);
	  }
	  List<String> scienceList = CommonMethods.listSciences();
	  String sciences = "";
	  for (final String s : scienceList) {
		 sciences += s + "\t" + sos.getSciencePoints(s + " points") + "\t" + sos.getScienceEffect(s + " effect") + "\n";
	  }
	  PreparedStatement ps = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("DELETE FROM sos WHERE name = ?");
		 ps.setString(1, sos.getName());
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e) {
		 }

		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("INSERT INTO sos VALUES (?,?,?,?,?,?)");
		 ps.setString(1, sos.getName());
		 ps.setString(2, sciences);
		 ps.setTimestamp(3, new Timestamp(sos.getUpdated().getTime()));
		 ps.setString(4, sos.getExportLine());
		 ps.setInt(5, sos.getTotalSciencePoints());
		 ps.setString(6, sos.getSavedBy());
		 ps.executeUpdate();

		 if (sos.getLand() != 0) {
			ps = DBConnectionFactory.getConnectionHandler().getConnection()
				  .prepareStatement("UPDATE province SET land = ? WHERE name = ?");
			ps.setInt(1, sos.getLand());
			ps.setString(2, sos.getName());
			ps.executeUpdate();
		 }
		 out = "SoS added";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Updates the province with information from the SoM
    * 
    * @param som
    */
   private static void updateProvince(final SoS sos) {
	  PreparedStatement ps = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("UPDATE province SET kdloc = ? WHERE name = ?");
		 ps.setString(1, sos.getKdloc());
		 ps.setString(2, sos.getName());
		 ps.executeUpdate();
		 ps.close();
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
   }
}
